package com.suning.sawp.test.clerk;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.mongodb.core.MongoOperations;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.testng.annotations.Test;

import com.suning.sawp.dto.clerk.InvitationCodeInfo;
import com.suning.sawp.po.promotion.CouponRecord;
import com.suning.sawp.service.util.DateUtils;
import com.suning.sawp.service.util.ExcelUtils;
import com.suning.sawp.service.util.GsonUtils;
import com.suning.sawp.service.util.HttpClientUtil;
import com.suning.sawp.test.BaseTest;

public class MyInvitationCodeImport extends BaseTest{
	@Resource
    MongoOperations mongoTemplate;
	
	@Test
    public void importInvitationCodeInfo(){
	    Query query = new Query();
	    Criteria criteria = new Criteria();
	    criteria.and("oaAccountNo").is("15071823");
	    query.addCriteria(criteria);
	    query.with(new Sort(Direction.DESC, "sendTime"));
	    
	    List<CouponRecord> list = mongoTemplate.find(query, CouponRecord.class);
	    List<String[]> datas = new ArrayList<String[]>();
	    String[] titles = {"发券时间", "会员手机号", "会员编码", "手机归属地", "所属运营商"};
	    for (CouponRecord record : list) {
	        String[] m = getMoiblePlace(record.getTelNo());
	        String[] s = {DateUtils.getDateStrByFormat(record.getSendTime(), 
	                DateUtils.DEFAULY_FULL_TIME_PATTERN), record.getTelNo(), record.getCustNo(),
	                m[0], m[1]};
	        datas.add(s);
	    }
	    Workbook workbook = ExcelUtils.createExcel(datas, titles);
	    try {
            FileOutputStream fos = new FileOutputStream(new File("D://15071823.xlsx"));
            workbook.write(fos);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
//    	mongoTemplate.remove(new Query(), InvitationCodeInfo.class);
//        List<InvitationCodeInfo> labelList = getInvitationCodeInfo("D://cc.xls", 0);
//        List<InvitationCodeInfo> errors = new ArrayList<InvitationCodeInfo>();
//        for (InvitationCodeInfo label : labelList) {
//            try {
//                mongoTemplate.insert(label,"InvitationCode");
//            } catch (Exception e) {
//                e.printStackTrace();
//                errors.add(label);
//            }
//        }
//        if (CollectionUtils.isNotEmpty(errors)) {
//            System.out.println("导入mongodb失败记录条数：" + errors.size());
//            createErrorTxt(errors);
//        }
//	    	MdmStoreInfo a= new MdmStoreInfo();
//	    	a.setBranchCode("11");
//	    	a.setStoreCode("22");
//	    	mongoTemplate.insert(a,"MdmStoreInfo");
    }
	
	private String[] getMoiblePlace(String mobile) {
	    String ss = HttpClientUtil.getData("http://www.ip138.com:8080/search.asp?mobile=" + mobile + "&action=mobile");
        int a = ss.indexOf("卡号归属地");
        int b = ss.indexOf("区 号");
        String sss = ss.substring(a, b).replace("<TD width=* align=\"center\" class=tdc2>", "")
                .replace("<TR class=tdc bgcolor=#EFF1F3>", "").replace("<TD width=\"138\" align=\"center\" noswap>", "")
                .replace("卡&nbsp;类&nbsp;型</TD>", "").replace("</TR>", "").replace("&nbsp;", "");
        String[] s = sss.split("</TD>");
        String place = s[1].trim().replace("<td align=\"center\" class=tdc2><!-- <td></td> -->", "");
        String u = s[2].trim().replace("<TD align=\"center\" class=tdc2><!-- <td></td> -->", "");
        
        String[] dd = {place, u};
        return dd;
	}
	
	private List<InvitationCodeInfo> getInvitationCodeInfo(String filePath, int sheetNo) {
    	List<InvitationCodeInfo> dataList = new ArrayList<InvitationCodeInfo>();
    	Map<String, String> recordMap = new HashMap<String, String>();
    	try{
          FileInputStream fis = new FileInputStream(filePath);
          HSSFWorkbook wookbook = new HSSFWorkbook(fis);
          HSSFSheet sheet = wookbook.getSheetAt(sheetNo);
          // 获取到Excel文件中的所有行数
          int rowsNum = sheet.getPhysicalNumberOfRows();
          // 从第6行开始遍历（模板前两行为无效值）
          for(int i = 1; i < rowsNum; i++) {
              HSSFRow row = sheet.getRow(i);
              String[] valueArr = null;
              // 行不为空
              if (null != row) {
                  // 获取到Excel文件中的所有的列
                  int cellsNum = row.getLastCellNum();
                  valueArr = new String[cellsNum];
                  // 遍历列
                  for (int j = 0; j < cellsNum; j++) {
                      // 获取到列的值
                      HSSFCell cell = row.getCell(j);
                      if (null != cell) {
                          switch (cell.getCellType()) {
                              case HSSFCell.CELL_TYPE_FORMULA :
                                  break;
                              case HSSFCell.CELL_TYPE_NUMERIC :
                            	  valueArr[j] = (cell.getNumericCellValue() + "")
                            	  	.substring(0, (cell.getNumericCellValue() + "").indexOf("."));
                                  break;
                              case HSSFCell.CELL_TYPE_STRING :
                            	  valueArr[j] = cell.getStringCellValue();
                                  break;
                              default:
                            	  valueArr[j] = "";
                                  break;
                          }
                      }
                  }
                  String key = valueArr[0]+valueArr[1]+valueArr[2]+valueArr[3];
                  if(!recordMap.containsKey(key)){
                	  InvitationCodeInfo codeBean = new InvitationCodeInfo();
                	  codeBean.setRegionCode(valueArr[0]);
                	  codeBean.setRegionName(valueArr[1]);
                	  codeBean.setChannelCode(valueArr[2]);
                	  codeBean.setQrCode(valueArr[3]);
                      dataList.add(codeBean);
                      recordMap.put(key, key);
                  }
              } else {
                  break;
              }
          }
    	} catch (Exception e){
    		e.printStackTrace();
    	}
          return dataList;
    }
	
	/**
     * 
     * 功能描述: 将导入mongodb失败的shopinfo以json格式生成txt<br>
     * 〈功能详细描述〉
     *
     * @param errors
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    @SuppressWarnings("resource")
	private void createErrorTxt(List<InvitationCodeInfo> errors) {
        try {
            FileWriter fw = new FileWriter("D://errorJson.txt");
            for (int i = 0; i < errors.size(); i++) {
                fw.write(GsonUtils.toJson(errors.get(i), false) + "\r\n");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
